﻿using LBSoft.Common.DotNetConfig;
using LBSoft.Common.DotNetEncrypt;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using LBSoft.Common.DotNetCode;
using System.Text;
using LBSoft.Common.DotNetExt;

namespace LBSoft.DataBase
{
    public partial class BaseDAL : IDbNewHelper
    {
        internal class _helper : SqlHelper { };//静态,单类型数据源时启用
        public string ConnStr_Read, ConnStr_Write;
        internal SqlTransaction Tran = null;
        public BaseDAL()
        {
            ConnStr_Read = _helper.ConnStr_Read;
            ConnStr_Write = _helper.ConnStr_Write;
        }

        public BaseDAL(string connectstring)
        {
            if (ConfigHelper.GetAppSettings("ConStringEncrypt") == "true")
            {
                ConnStr_Read = DESEncrypt.Decrypt(connectstring);
                ConnStr_Write = DESEncrypt.Decrypt(connectstring);
            }
            else
            {
                ConnStr_Read = connectstring;
                ConnStr_Write = connectstring;
            }
        }

        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            if (Tran == null || Tran.Connection == null)
                return _helper.ExecuteNonQuery(ConnStr_Write, cmdType, cmdText, commandParameters);
            else
                return _helper.ExecuteNonQuery(Tran, cmdType, cmdText, commandParameters);
        }

        public object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            if (Tran == null || Tran.Connection == null)
                return _helper.ExecuteScalar(ConnStr_Write, cmdType, cmdText, commandParameters);
            else
                return _helper.ExecuteScalar(Tran, cmdType, cmdText, commandParameters);
        }
        public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            if (Tran == null || Tran.Connection == null)
                return _helper.ExecuteReader(ConnStr_Write, cmdType, cmdText, commandParameters);
            else
                return _helper.ExecuteReader(Tran, cmdType, cmdText, commandParameters);
        }
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            if (Tran == null || Tran.Connection == null)
                return _helper.ExecuteDataSet(ConnStr_Write, cmdType, cmdText, commandParameters);
            else
                return _helper.ExecuteDataSet(Tran, cmdType, cmdText, commandParameters);
        }
        public bool MsSqlBulkCopyData(DataTable dt)
        {
            return _helper.MsSqlBulkCopyData(ConnStr_Write, dt);
        }
        public string[] getProcedureParams(string procedurename)
        {
            string sql = "select a.[name] from syscolumns a inner join sysobjects c on a.id=c.id where c.[name] = @procedurename";
            SqlParameter p = new SqlParameter("@procedurename", procedurename);
            List<string> list = null;
            using (SqlDataReader dr = _helper.ExecuteReader(_helper.ConnStr_Read, CommandType.Text, sql, p))
            {
                if (dr.HasRows)
                {
                    list = new List<string>();
                    while (dr.Read())
                        list.Add(dr.GetString(0).ToLower());
                }
            }
            return list != null ? list.ToArray() : null;
        }

        #region SqlParameter
        public object GetParamValue(object value)
        {
            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                return value;
            return (object)DBNull.Value;
        }
        public SqlParameter GetParam(string paramName, object value)
        {
            return new SqlParameter(paramName, GetParamValue(value));
        }
        public SqlParameter GetParam(string paramName, SqlDbType type, object value)
        {
            SqlParameter param = new SqlParameter(paramName, type);
            param.Value = GetParamValue(value);
            return param;
        }
        #endregion

        public DataTable GetPageList(string sql, SqlParam[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder sb = new StringBuilder();
            DataTable result;
            try
            {
                int num = (pageIndex - 1) * pageSize;
                int num2 = pageIndex * pageSize;
                sb.Append("Select * From (Select ROW_NUMBER() Over (Order By " + orderField + " " + orderType);
                sb.Append(string.Concat(new object[]
                {
                    ") As rowNum, * From (",
                    sql,
                    ") As T ) As N Where rowNum > ",
                    num,
                    " And rowNum <= ",
                    num2
                }));

                count = this.ExecuteScalar(CommandType.Text, "Select Count(1) From (" + sql + ") As t", null).ToInt32(0);
                DataSet ds = this.ExecuteDataSet(CommandType.Text, sb.ToString(), null);
                if (ds != null && ds.Tables.Count > 0)
                {
                    result = ds.Tables[0];
                }
                else
                {
                    result = null;
                }

                //count = this.GetObjectValue(new StringBuilder("Select Count(1) From (" + sql + ") As t"), param).ToInt32(0);
                //result = this.GetDataTableBySQL(sb, param);
            }
            catch (Exception e)
            {
                result = null;
            }
            return result;
        }

        public DataTable GetPageList(string sql, SqlParam[] param, string order, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder sb = new StringBuilder();
            DataTable result;
            try
            {
                int num = (pageIndex - 1) * pageSize;
                int num2 = pageIndex * pageSize;
                sb.Append("Select * From (Select ROW_NUMBER() Over (Order By " + order);
                sb.Append(string.Concat(new object[]
                {
                    ") As rowNum, * From (",
                    sql,
                    ") As T ) As N Where rowNum > ",
                    num,
                    " And rowNum <= ",
                    num2
                }));

                count = this.ExecuteScalar(CommandType.Text, "Select Count(1) From (" + sql + ") As t", null).ToInt32(0);
                DataSet ds = this.ExecuteDataSet(CommandType.Text, sb.ToString(), null);
                if (ds != null && ds.Tables.Count > 0)
                {
                    result = ds.Tables[0];
                }
                else
                {
                    result = null;
                }

                //count = this.GetObjectValue(new StringBuilder("Select Count(1) From (" + sql + ") As t"), param).ToInt32(0);
                //result = this.GetDataTableBySQL(sb, param);
            }
            catch (Exception e)
            {
                result = null;
            }
            return result;
        }

    }
}
